import config
from config import log
from config import runsql,runsqlmany
import openpyxl
import pathlib
import sqlite3
from datetime import datetime
from decimal import Decimal
from openpyxl import Workbook



root=pathlib.Path(__file__).parent
db=sqlite3.connect(str(root/"statistics.db"))
cur = db.cursor()



    
def table1():
    sql="SELECT 学校 FROM source GROUP BY 学校  ORDER BY 区域 ASC,学校  ASC"
    result1=runsql(sql)
    result1=result1.fetchall()
    schoolList=[]
    schooldata={}

    sheetcolbase=2
    sheetrowbase=3

    for i in result1:
        schoolList.append(i[0])

    wb=Workbook()
    ws=wb.active
    ws.title="初中"

    row=sheetrowbase
    col=1
    for i in schoolList: #写入校名
        ws.cell(row=row,column=col).value=i
        row+=1

    col=sheetcolbase
    for i in config.subject:
        sql=f"SELECT `学校`, Avg(`总分`) AS 总分 FROM convertedScore WHERE `年级` = '{i}' and 总分 >=0  GROUP BY `学校` ORDER BY 总分 DESC;"
        result1=runsql(sql)
        result1=result1.fetchall()
        n=1
        schoolRank={}
        inyear={
            "七年级":int(datetime.now().year-1),
            "八年级":int(datetime.now().year-2),
            "九年级":int(datetime.now().year-3),
        }
        for k in result1:
            schoolRank[k[0]]=[k[1],n]
            n+=1
        print()
        print(schoolRank)
        for k in schoolRank:
            schooldata[k]={i:{"outavg":schoolRank[k][0],"outrank":schoolRank[k][1]}}
         







        row=sheetrowbase
        log.info(f"统计{i}")
        for k in schoolList:
            if k in schoolRank :
                ws.cell(row=row,column=col+4).value=schoolRank[k][0]
                ws.cell(row=row,column=col+5).value=schoolRank[k][1]
                ws.cell(row=row,column=col+6).value=100-(schoolRank[k][1]-1)*5
            row+=1
        col+=8
    if(not wbsave(wb,root/"1、综合评价.xlsx")):
        log.error("文件保存失败")
        config._exit("文件保存失败",5)

def wbsave(wb,path,n=3):
    while(n):
        n-=1
        try:
            wb.save(path)
            return True
        except PermissionError:
            log.warning(f"请关闭文件{path}后保存,按Enter继续")
            input()
        except Exception as e :
            log.warning(f"{e}")
    return False
if __name__ =="__main__":
    table1()
    config._exit()